﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Chire.ChireInter.School
{
    public class School_Action
    {
        #region 获取所有的学校
        public List<School_Model> getAllSchool()
        {
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();

            string strselect = "select * from school";
            //查询音乐信息
            DataTable dt = new DataTable();

            SqlDataAdapter dap = new SqlDataAdapter(strselect, sqlcon);
            dap.Fill(dt);
            int rows = dt.Rows.Count;

            List<School_Model> schoolList = new List<School_Model>();
            for (int i = 0; i < rows; i++)
            {
                string id = dt.Rows[i]["id"].ToString();
                string school = dt.Rows[i]["school"].ToString();
                string lat = dt.Rows[i]["lat"].ToString();
                string lon = dt.Rows[i]["lon"].ToString();
                string map_img = dt.Rows[i]["map_img"].ToString();

                
                School_Model schoolModel = new School_Model()
                {
                    id = id,
                    school = school,
                    lat = lat,
                    lon = lon,
                    map_img = map_img,
                };
                schoolList.Add(schoolModel);
            }
            sqlcon.Close();
            return schoolList;
        }
        #endregion

        #region 删除学校
        public void deleteSchoolWithId(string id) {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";
            StrInsert = "delete from school where id = '" + id + "'";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

        #region 插入学校
        public void insertSchool(string school, string lat, string lon,string img)
        {
            // 连接数据库
            SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
            sqlcon.Open();
            string StrInsert = "";

            StrInsert = "insert into school(school,lat,lon,map_img) values (@school,@lat,@lon,@map_img)";
            SqlCommand cmd = new SqlCommand(StrInsert, sqlcon);
            // 添加参数并且设置参数值

            // 1. 作者id
            cmd.Parameters.Add("@school", SqlDbType.VarChar, 50);
            cmd.Parameters["@school"].Value = school;
            // 1. 日记标题
            cmd.Parameters.Add("@lat", SqlDbType.VarChar, 50);
            cmd.Parameters["@lat"].Value = lat;
            // 2.日记详情
            cmd.Parameters.Add("@lon", SqlDbType.VarChar, 50);
            cmd.Parameters["@lon"].Value = lon;

            cmd.Parameters.Add("@map_img", SqlDbType.VarChar, 350);
            cmd.Parameters["@map_img"].Value = img;
            
            // 执行插入数据的操作
            cmd.ExecuteNonQuery();
            sqlcon.Close();
        }
        #endregion

    }
}